if not exists (select * from sys.objects where object_id = OBJECT_ID(N'[dbo].[ImagesInfo]') and type in (N'U'))
begin
	create table ImagesInfo
	(
		Id int identity(1,1) not null,
		Path nvarchar(max) not null,
		CreatedBy int not null,
		Created datetime not null default getutcdate()
	)
	
	alter table ImagesInfo add constraint PK_ImagesInfo_Id
	primary key (Id)
	
	alter table ImagesInfo add constraint FK_CreatedBy_Users_UserId
	foreign key (CreatedBy)
	references dbo.Users ([UserId])
end

if exists (select * from sys.objects where object_id = OBJECT_ID(N'SPImagesInfoInsert') AND type in (N'P', N'PC'))
	drop procedure SPImagesInfoInsert
go

create procedure SPImagesInfoInsert(@path nvarchar(max), @editorUserId int, @imageId int output)
as
	set nocount on;
	
	insert into ImagesInfo(Path, CreatedBy) values(@path, @editorUserId)
	
	select @imageId = SCOPE_IDENTITY()
go

if exists (select * from sys.objects where object_id = OBJECT_ID(N'SPImagesInfoGetById') AND type in (N'P', N'PC'))
	drop procedure SPImagesInfoGetById
go

create procedure SPImagesInfoGetById(@id int)
as
	select
		Id,
		Path
	from
		ImagesInfo
	where
		Id = @id
go

if not exists(select * from sys.columns where Name = N'AvatarId' and Object_ID = Object_ID(N'[dbo].[Users]'))    
begin
	alter table Users
		add AvatarId int
		
	alter table Users
		add constraint FK_AvatarId_ImagesInfo_Id 
		foreign key (AvatarId) references dbo.ImagesInfo (Id)
end

if exists (select * from sys.objects where object_id = OBJECT_ID(N'SPUsersUpdateAvatar') and type in (N'P', N'PC'))
	drop procedure SPUsersUpdateAvatar
go

create procedure SPUsersUpdateAvatar(@userId int, @avatarId int)
as
	update Users set AvatarId = @avatarId where UserId = @userId
go

if exists (select * from sys.objects where object_id = OBJECT_ID(N'SPUsersSelectByName') and type in (N'P', N'PC'))
	drop procedure SPUsersSelectByName
go

create procedure SPUsersSelectByName
	@UserName nvarchar(50)	
AS
SELECT
	U.UserId
	,U.UserName
	,U.UserProfile
	,U.UserSignature
	,U.UserGroupId
	,U.UserBirthDate
	,U.UserWebsite
	,U.UserTimezone
	,U.AvatarId
	,U.UserRegistrationDate
	,UG.UserGroupId
	,UG.UserGroupName
FROM
	Users U
	INNER JOIN UsersGroups UG ON UG.UserGroupId = U.UserGroupId
WHERE
	U.UserName = @UserName and U.Active = 1
	
go


if exists(select * from sys.columns 
            where Name = N'UserPhoto' and Object_ID = Object_ID(N'Users'))    
begin
    alter table Users drop column UserPhoto
end

go

ALTER PROCEDURE SPUsersGet
	@UserId int=11
AS
SELECT
	U.UserId
	,U.UserName
	,U.UserProfile
	,U.UserSignature
	,U.UserGroupId
	,U.UserBirthDate
	,U.UserWebsite
	,U.UserTimezone
	--,U.UserPhoto
	,U.AvatarId
	,U.UserRegistrationDate
	,U.UserExternalProfileUrl
	,U.UserEmail
	,U.UserEmailPolicy
	,UG.UserGroupId
	,UG.UserGroupName
FROM
	Users U
	INNER JOIN UsersGroups UG ON UG.UserGroupId = U.UserGroupId
WHERE
	U.UserId = @UserId
	
go

ALTER VIEW MessagesComplete
AS
SELECT
	M.TopicId
	,M.MessageId
	,M.MessageBody
	,M.MessageCreationDate
	,M.MessageLastEditDate
	,M.ParentId
	,M.UserId
	,M.Active
	,U.UserName
	,U.UserSignature
	,U.UserGroupId
	,G.UserGroupName
	--,U.UserPhoto
	,U.AvatarId
	,U.UserRegistrationDate
FROM
	Messages M
	INNER JOIN Users U ON U.UserId = M.UserId
	INNER JOIN UsersGroups G ON G.UserGroupId = U.UserGroupId
	LEFT JOIN Messages P ON P.TopicId = M.TopicId AND P.MessageId = M.ParentId AND P.Active = 1
GO

ALTER PROCEDURE SPMessagesGetByTopic
	@TopicId int
	,@ReaderUserId int = null
	,@PageSize int
	,@PageNumber int
AS

	declare @startRow int, @endRow int;
	set @startRow = (@PageNumber - 1) * @PageSize + 1;
	set @endRow = @PageNumber * @PageSize;

	if @ReaderUserId is not null begin
			with pagedMessages as
			(
				select 
					M.TopicId
					,M.MessageId
					,R.UserId as ReadBy
					,ROW_NUMBER() OVER(ORDER BY M.TopicId, M.MessageId asc) as RowNumber
				from 
					MessagesComplete M
				left join dbo.ReadMessages R
					on M.MessageId=R.MessageId 
						and M.TopicId=R.TopicId
						and R.UserId = @ReaderUserId
				where 
					M.TopicId = @TopicId
			)
			insert into ReadMessages(TopicId, MessageId, UserId)
			select
				TopicId
				,MessageId
				,@ReaderUserId
			from
				pagedMessages
			where
				RowNumber between @startRow and @endRow
				and ReadBy is null
	end;
		

	with pagedMessages as
	(
		SELECT 
			ROW_NUMBER() OVER (ORDER BY M.TopicId, M.MessageId asc) AS RowNumber
			,M.TopicId
			,M.MessageId
			,M.MessageBody
			,M.MessageCreationDate
			,M.MessageLastEditDate
			,M.ParentId
			,M.UserId
			,M.UserName
			,M.UserSignature
			,M.UserGroupId
			,M.UserGroupName
			--,M.UserPhoto
			,M.AvatarId
			,M.UserRegistrationDate
			,M.Active
			,COUNT(*) OVER() AS TotalMessages
		FROM 
			MessagesComplete M
		WHERE 
			M.TopicId = @TopicId
	)
	select
		TopicId
		,MessageId
		,MessageBody
		,MessageCreationDate
		,MessageLastEditDate
		,ParentId
		,UserId
		,UserName
		,UserSignature
		,UserGroupId
		,UserGroupName
		--,UserPhoto
		,AvatarId
		,UserRegistrationDate
		,Active
		,TotalMessages
	from
		pagedMessages
	where
		RowNumber between @startRow and @endRow

GO


ALTER PROCEDURE [dbo].[SPUsersInsertFromProvider]
	(@UserName nvarchar(50)
	,@UserProfile nvarchar(max)
	,@UserSignature nvarchar(max)
	,@UserGroupId smallint
	,@UserBirthDate datetime
	,@UserWebsite nvarchar(255)
	,@UserGuid char(32)
	,@UserTimezone decimal(9,2)
	,@UserEmail nvarchar(100)
	,@UserEmailPolicy int
	--,@UserPhoto nvarchar(1024)
	,@UserExternalProfileUrl nvarchar(255)
	,@UserProvider nvarchar(32)
	,@UserProviderId nvarchar(64))
AS

	--If it is the first active user -> make it an admin
	DECLARE @UserCount int
	SELECT @UserCount=COUNT(UserId) FROM Users WHERE Active = 1
	IF ISNULL(@UserCount, 0) = 0
		SELECT @UserGroupId = MAX(UserGroupId) FROM UsersGroups


	INSERT INTO Users
	   (UserName
	   ,UserProfile
	   ,UserSignature
	   ,UserGroupId
	   ,Active
	   ,UserBirthDate
	   ,UserWebsite
	   ,UserGuid
	   ,UserTimezone
	   ,UserEmail
	   ,UserEmailPolicy
	   --,UserPhoto
	   ,UserRegistrationDate
	   ,UserExternalProfileUrl
	   ,UserProvider
	   ,UserProviderId
	   ,UserProviderLastCall)
	VALUES
		(@UserName
	   ,@UserProfile
	   ,@UserSignature
	   ,@UserGroupId
	   ,1 --Active
	   ,@UserBirthDate
	   ,@UserWebsite
	   ,@UserGuid
	   ,@UserTimezone
	   ,@UserEmail
	   ,@UserEmailPolicy
	   --,@UserPhoto
	   ,GETUTCDATE() --RegitrationDate
	   ,@UserExternalProfileUrl
	   ,@UserProvider
	   ,@UserProviderId
	   ,GETUTCDATE() --UserProviderLastCall
		);

	DECLARE @UserId int;
	SELECT @UserId = @@IDENTITY;
	SELECT 	
		U.UserId
		,U.UserName
		,U.UserGroupId
		,U.UserGuid
		,U.UserTimeZone
		,U.UserExternalProfileUrl
		,U.UserProviderLastCall
		,U.UserEmail
	FROM
		Users U
	WHERE
		U.UserId = @UserId;
go

ALTER procedure SPMessagesGetById
	@TopicId int
	,@MessageId int
AS
	set nocount on;

	select
		[TopicId]
      ,[MessageId]
      ,[MessageBody]
      ,[MessageCreationDate]
      ,[MessageLastEditDate]
      ,[ParentId]
      ,[UserId]
      ,[Active]
      ,[UserName]
      ,[UserSignature]
      ,[UserGroupId]
      ,[UserGroupName]
      --,[UserPhoto]
      ,[UserRegistrationDate]
	from 
		MessagesComplete
	where
		TopicId = @TopicId
		and MessageId = @MessageId
go

ALTER PROCEDURE SPUsersUpdate
	@UserId int
	,@UserName nvarchar(50)
	,@UserProfile nvarchar(max)
	,@UserSignature nvarchar(max)
	,@UserBirthDate datetime
	,@UserWebsite nvarchar(255)
	,@UserTimezone decimal(9,2)
	,@UserEmail nvarchar(100)
	,@UserEmailPolicy int
	--,@UserPhoto nvarchar(1024)
	,@UserExternalProfileUrl nvarchar(255)
AS

	UPDATE Users
	SET 
	UserName = @UserName
	,UserProfile = @UserProfile
	,UserSignature = @UserSignature
	,UserBirthDate = @UserBirthDate
	,UserWebsite = @UserWebsite
	,UserTimezone = @UserTimezone
	,UserEmail = @UserEmail
	,UserEmailPolicy = @UserEmailPolicy
	--,UserPhoto = @UserPhoto
	,UserExternalProfileUrl = @UserExternalProfileUrl
	WHERE 
		UserId = @UserId;
	
go

if exists (select * from sys.objects where object_id = OBJECT_ID(N'SPImagesInfoRemoveById') and type in (N'P', N'PC'))
	drop procedure SPImagesInfoRemoveById
go

create procedure SPImagesInfoRemoveById
	@id int
as
	delete from ImagesInfo where Id=@id
go

alter procedure SPUsersGetAll
AS
SELECT
	U.UserId
	,U.UserName
	,U.UserProfile
	,U.UserSignature
	,U.UserGroupId
	,U.UserBirthDate
	,U.UserWebsite
	,U.UserTimezone
	,U.AvatarId
	,U.UserRegistrationDate
	,U.UserProviderId
	,UG.UserGroupId
	,UG.UserGroupName
FROM
	Users U
	INNER JOIN UsersGroups UG ON UG.UserGroupId = U.UserGroupId
WHERE
	U.Active = 1
ORDER BY 
	U.UserName
go